package com.xh.web.service.impl;

import java.sql.*;
import java.util.*;
import java.util.Date;

import com.xh.web.domain.*;
import com.xh.web.domain.vo.DbInfo;
import com.xh.web.domain.vo.ImportColumn;
import com.xh.web.domain.vo.ImportTable;
import com.xh.web.domain.vo.SyncDbVo;
import com.xh.web.mapper.*;
import com.xh.web.service.IDDbConnectService;
import com.xh.web.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


/**
 * 业务名Service业务层处理
 * 
 * @author 宁兴海
 * @date 2022-07-27
 */
@Service
public class DDbConnectServiceImpl implements IDDbConnectService
{
    @Autowired(required = false)
    private DDbConnectMapper dDbConnectMapper;

    @Autowired(required = false)
    private DTableMapper dTableMapper;

    @Autowired(required = false)
    private DColumnMapper dColumnMapper;

    @Autowired(required = false)
    private DTableIndexMapper dTableIndexMapper;

    @Autowired(required = false)
    private DProjectMapper dProjectMapper;



    /**
     * 查询业务名
     * 
     * @param id 业务名ID
     * @return 业务名
     */
    @Override
    public DDbConnect selectDDbConnectById(String id)
    {
        return dDbConnectMapper.selectDDbConnectById(id);
    }

    /**
     * 查询业务名列表
     * 
     * @param dDbConnect 业务名
     * @return 业务名
     */
    @Override
    public List<DDbConnect> selectDDbConnectList(DDbConnect dDbConnect)
    {
        return dDbConnectMapper.selectDDbConnectList(dDbConnect);
    }

    /**
     * 新增业务名
     * 
     * @param dDbConnect 业务名
     * @return 结果
     */
    @Override
    public int insertDDbConnect(DDbConnect dDbConnect)
    {
        if(StringUtils.isNotEmpty(dDbConnect.getId())){
            dDbConnect.setUpdateDate(new Date());
            return dDbConnectMapper.updateDDbConnect(dDbConnect);
        }
        dDbConnect.setCreateBy("admin");
        dDbConnect.setCreateDate(new Date());
        dDbConnect.setUpdateBy("admin");
        dDbConnect.setUpdateDate(new Date());
        dDbConnect.setDelFlag("0");
        return dDbConnectMapper.insertDDbConnect(dDbConnect);
    }

    /**
     * 修改业务名
     * 
     * @param dDbConnect 业务名
     * @return 结果
     */
    @Override
    public int updateDDbConnect(DDbConnect dDbConnect)
    {
        return dDbConnectMapper.updateDDbConnect(dDbConnect);
    }

    /**
     * 批量删除业务名
     * 
     * @param ids 需要删除的业务名ID
     * @return 结果
     */
    @Override
    public int deleteDDbConnectByIds(String[] ids)
    {
        return dDbConnectMapper.deleteDDbConnectByIds(ids);
    }

    /**
     * 删除业务名信息
     * 
     * @param id 业务名ID
     * @return 结果
     */
    @Override
    public int deleteDDbConnectById(String id)
    {
        return dDbConnectMapper.deleteDDbConnectById(id);
    }

    public void connInport(String connId,String projectId) throws SQLException, ClassNotFoundException {
        DDbConnect dDbConnect=dDbConnectMapper.selectDDbConnectById(connId);
        Connection conn= DbUtil.getConn(dDbConnect);

        DbInfo dbInfo = new DbInfo();
        List<ImportTable> importTables = getTables(conn,dDbConnect);
        if (importTables.size() > 0) {
            for (ImportTable table : importTables) {
                List<ImportColumn> importColumnList=getColumns(table.getTableCode(),conn,dDbConnect);
                table.setImportColumns(importColumnList);
                table.setColumnNum(importColumnList.size());
            }
        }
        //List<ImportIndex> importIndexs = getIndexs();
        dbInfo.setTables(importTables);
        DbUtil.close(conn);

        //导入
        //删除项目下所有的表和列
        dTableMapper.deleteAllTableByProjectId(projectId,null);
        dColumnMapper.deleteAllColumnByProjectId(projectId,null);
        //新写：删除项目下所有的索引
        dTableIndexMapper.deleteAllIndexByProjectId(projectId,null);

        if (importTables.size() > 0) {
            for (ImportTable table : importTables) {
                String tableId = insertTableImport(projectId,table.getTableName(),table.getTableCode(),
                        table.getColumnNum());
                for (ImportColumn column:table.getImportColumns()){
                    insertColumnImport(projectId,tableId,table.getTableCode(),column);
                }
            }
        }
        DProject dProject=dProjectMapper.selectDProjectById(projectId);
        dProject.setTableNum(dTableMapper.selectTableNumByProjectId(dProject.getId()));
        dProject.setUpdateDate(DateUtils.getNowDate());
        dProjectMapper.updateDProject(dProject);
    }

    private String insertTableImport(String projectId, String tableComment, String tableName, long columnNum) {
        DTable dTable = new DTable();
        dTable.setProjectId(projectId);
        dTable.setTableCode(tableName);
        dTable.setTableName(tableComment);
        dTable.setColumnNum(new Long(columnNum));
        //dTable.setContentId("");
        dTable.setCreateName("admin");
        dTable.setUpdateName("admin");
        dTable.setCreateBy("admin");
        dTable.setCreateDate(DateUtils.getNowDate());
        dTable.setUpdateBy("admin");
        dTable.setUpdateDate(DateUtils.getNowDate());
        dTable.setDelFlag("0");
        dTable.setdSort(new Long(0));

        dTableMapper.insertDTable(dTable);
        //表的id是需要返回的
        return dTable.getId();
    }

    private void insertColumnImport(String projectId,String tableId,String tableCode,ImportColumn importColumn) {
        //dColumn.setId(IdUtils.fastUUID());
        DColumn dColumn=new DColumn();
        dColumn.setProjectId(projectId);
        dColumn.setTableId(tableId);
        dColumn.setTableCode(tableCode);
        dColumn.setColumnCode(importColumn.getColumnCode().toLowerCase());
        dColumn.setColumnName(importColumn.getColumnName());
        dColumn.setDataType(importColumn.getDataType().toLowerCase());
        if(!GenUtils.arraysContains(GenConstants.COLUMNTYPE_TIME, dColumn.getDataType())){
            dColumn.setdLength(importColumn.getdLength());
        }
        //dColumn.setdLength(userEntities.getdLength());
        dColumn.setIsEmpty(importColumn.getIsEmpty());
        dColumn.setIsPrimarykey(importColumn.getIsPrimarykey());
        dColumn.setDefaultValue(importColumn.getDefaultValue());
        dColumn.setCreateBy("admin");
        dColumn.setCreateDate(new Date());
        dColumn.setUpdateBy("admin");
        dColumn.setUpdateDate(new Date());
        dColumn.setDelFlag("0");
        dColumn.setdSort(new Long(0));
        dColumnMapper.insertDColumn(dColumn);
    }


    public List<ImportTable> getTables(Connection connection,DDbConnect dDbConnect) throws SQLException {
        List<ImportTable> importTables = new ArrayList<>();
        ResultSet tables=null;
        try {
            //DatabaseMetaData metaData = connection.getMetaData();
            //String catalog = connection.getCatalog();
            //String userName = metaData.getUserName();
            Statement statement=connection.createStatement();
            String sql="";
            if(dDbConnect.getDbType().equals("mysql")){
                sql="select table_name,table_comment from information_schema.tables where table_schema = \'"+dDbConnect.getDbName()+"\'";
            }else if(dDbConnect.getDbType().equals("oracle")){
                sql="select a.table_name,b.comments as table_comment from user_tables a,user_tab_comments b where a.table_name=b.table_name order by table_name";
            }else if(dDbConnect.getDbType().equals("dm")){
                sql="select distinct a.table_name,b.comments as table_comment from user_tables a,user_tab_comments b where a.table_name=b.table_name order by table_name";
            }

            tables =statement.executeQuery(sql);
            while (tables.next()) {
                String tableName = tables.getString("table_name");
                String tableRemark = tables.getString("table_comment");
                ImportTable importTable = new ImportTable();
                importTable.setTableCode(tableName.toLowerCase());
                importTable.setTableName(tableRemark);

                importTables.add(importTable);
            }
        } finally {
            DbUtil.closeRs(tables);
        }
        importTables.sort((t1, t2) -> t1.getTableCode().compareTo(t2.getTableCode()));
        return importTables;
    }


    public List<ImportColumn> getColumns(String tableName,Connection connection,DDbConnect dDbConnect) throws SQLException {
        List<ImportColumn> importColumns = new ArrayList<>();
        ResultSet columns=null;
        try {
            if(dDbConnect.getDbType().equals("oracle")||dDbConnect.getDbType().equals("dm")){
                Statement statement=connection.createStatement();
                String sql="";
                sql="select b.column_name,b.comments,a.data_type,a.data_length,a.nullable,a.data_default "
                +"from user_tab_columns a,user_col_comments b "
                +"where a.table_name=b.table_name and a.column_name=b.column_name "
                +"and a.table_name=\'"+tableName.toUpperCase()+"\'";

                columns =statement.executeQuery(sql);
                while (columns.next()) {
                    ImportColumn column = new ImportColumn();
                    column.setColumnCode(columns.getString("column_name"));
                    column.setColumnName(columns.getString("comments"));
                    column.setDataType(columns.getString("data_type"));
                    if(DbUtil.isGetLength(dDbConnect.getDbType(),column.getDataType())){
                        column.setdLength(columns.getString("data_length"));
                    }
                    column.setDefaultValue(columns.getString("data_default"));
                    column.setIsEmpty(columns.getString("nullable").equals("Y") ? "0" : "1");

                    column.setIsPrimarykey("0");

                    importColumns.add(column);
                }
                return importColumns;
            }


            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            String userName = metaData.getUserName();

            ResultSet primaryKeys = metaData.getPrimaryKeys(catalog, userName, tableName);
            Set<String> primaryColumns = new HashSet<>();
            while (primaryKeys.next()) {
                primaryColumns.add(primaryKeys.getString("COLUMN_NAME"));
            }

            columns = metaData.getColumns(catalog, userName, tableName, null);
            while (columns.next()) {
                ImportColumn column = new ImportColumn();
                String column_name = columns.getString("COLUMN_NAME");
                column.setColumnCode(column_name);
                column.setColumnName(columns.getString("REMARKS"));
                column.setDataType(columns.getString("TYPE_NAME"));
                column.setdLength(columns.getString("COLUMN_SIZE"));
                column.setDefaultValue(columns.getString("COLUMN_DEF"));
                column.setIsEmpty(columns.getString("IS_NULLABLE").equals("YES") ? "0" : "1");

                column.setIsPrimarykey(primaryColumns.contains(column_name) ? "1" : "0");

                importColumns.add(column);
            }
        } finally {
            DbUtil.closeRs(columns);
        }
        return importColumns;
    }

    public SyncDbVo getLocalDbInfoById(String id,String tableCode){
        SyncDbVo syncDbVo=  new SyncDbVo();
        DTable dTable =new DTable();
        dTable.setProjectId(id);
        dTable.setTableCode(tableCode);
        List<DTable> dTableList=dTableMapper.selectDTableList(dTable);
        syncDbVo.setdTableList(dTableList);

        DColumn dColumn=new DColumn();
        dColumn.setProjectId(id);
        dColumn.setTableCode(tableCode);
        List<DColumn> dColumnList=dColumnMapper.selectDColumnList(dColumn);
        syncDbVo.setdColumnList(dColumnList);

        DTableIndex dTableIndex =new DTableIndex();
        dTableIndex.setProjectId(id);
        dTableIndex.setTableCode(tableCode);
        List<DTableIndex> dTableIndexList=dTableIndexMapper.selectDTableIndexList(dTableIndex);
        syncDbVo.setdTableIndexList(dTableIndexList);

        return syncDbVo;
    }
}
